-- *** 판매 게시판/거래 내역/판매 게시판의 핫 키워드 SQL문 ***
--																					by Harry, 5idiots

-- 1. safari_sellboard 

-- (1) safari_sellboard 테이블 생성
create table safari_sellboard(
	sell_no number  primary key, -- 판매게시판 글 번호
	sell_content varchar2(3000) not null, -- 판매게시판 글 내용
	sell_title varchar2(50) not null, -- 판매게시판 글 제목
	sell_time_posted date not null, -- 판매게시판 글 작성시간
	sell_hits number default 0, -- 판매게시판 글 조회수
	sell_status varchar2(50) default '판매중', -- 판매게시판 상품 판매여부
	sell_org_filename varchar2(50), -- 판매게시판 사진파일 원본 이름
	sell_dest_filename varchar2(50), -- 판매게시판 사진파일 변경된 이름 
	sell_product_name varchar2(50) not null, -- 판매게시판 상품명
	sell_product_price number not null, -- 판매게시판 상품 가격
	sell_trade_area varchar2(50) not null, -- 판매게시판 거래가능 지역
	id varchar2(20) not null, -- 외래키 회원 아이디 : safari_member 테이블의 기본키 참조
	constraint fk_safari_member_id foreign key(id) references safari_member(id) on delete cascade
)

alter table safari_sellboard modify(id varchar2(100) not null)

-- (2) safari_sellboard : 외래키 제약조건 추가
alter table safari_sellboard 
add constraint fk_safari_member_id foreign key(id) 
references safari_member(id) 
on delete cascade;

-- (2) safari_sellboard 테이블 조회
select * from safari_sellboard;

-- (3) safari_sellboard 테이블 제거
drop table safari_sellboard;

-- (4) safari_sellboard_seq 시퀀스 생성
create sequence safari_sellboard_seq nocache;

-- (5) safari_sell_board_seq 시퀀스 조회
select safari_sellboard_seq.nextval from dual;
select safari_sellboard_seq.currval from dual;

-- (6) safari_sellboard_seq 시퀀스 제거
drop sequence safari_sellboard_seq;


-- 2. safari_trade_history

-- (1) safari_trade_history 테이블 생성
create table safari_trade_history(
	trade_history_no number  primary key, -- 전체거래내역 글번호
	time_traded date not null, -- 전체거래내역 거래된 시간
	trade_seller varchar2(50) not null, -- 전체거래내역 판매자
	trade_buyer varchar2(50) not null, -- 전체거래내역 구매자
	sell_no number not null, -- 외래키 판매게시판 글 번호 : safari_sellboard 테이블의 기본키 참조
	constraint fk_safari_sell_no foreign key(sell_no) references safari_sellboard(sell_no)
)

-- (2) safari_trade_history 테이블 조회
select * from safari_trade_history;

-- (3) safari_trade_history 테이블 제거
drop table safari_trade_history;

-- (4) safari_trade_history 시퀀스 생성
create sequence safari_trade_history_seq nocache;

-- (5) safari_trade_history 시퀀스 조회
select safari_trade_history_seq.nextval from dual;
select safari_trade_history_seq.currval from dual;

-- (6) safari_trade_history 시퀀스 제거
drop sequence safari_trade_history_seq;


-- 3. safari_sellboard_log

-- (1) safari_sellboard_log 테이블 생성
create table safari_sellboard_log(
	sell_hotkeyword varchar2(50) primary key,
	sell_count number default 0
)

-- (2) safari_sellboard_log 테이블 조회
select sell_hotkeyword, rank from(
	select sell_hotkeyword, ceil(rownum/5) as page, rank from(
		select sell_hotkeyword, rank() over (order by sell_count desc) as rank from safari_sellboard_log
	)
)where page=1;

-- (3) safari_sellboard_log 테이블 제거
drop table safari_sellboard_log;


-- 4. 테스트 SQL문
 
-- (1) safari_sellboard 테이블에 데이터 입력
insert into safari_sellboard
	(sell_no, sell_content, sell_title, sell_time_posted, sell_org_filename, sell_dest_filename, 
	sell_product_name, sell_product_price, sell_trade_area, id)
		values(safari_sellboard_seq.nextval, '테스트', '테스트', sysdate,
		'원본파일명', '변경파일명', '노트북', 10, '서울', 'dsf');

-- (2) safari_sellbaord 테이블 조회(PagingBean이 적용된 목록을 조회할때 사용)
select sell_no, sell_title, id, name, sell_time_posted, sell_hits
	from(
	select sell_no, sell_title, id, name, sell_time_posted, sell_hits, ceil(rownum/5) as page 
		from(
		select s.sell_no, s.sell_title, m.id, m.name, s.sell_time_posted, s.sell_hits 
			from safari_sellboard s, safari_member m
				where s.id=m.id
				order by sell_no desc)
					)where page=1;

-- (3) safari_sellboard 테이블의 전체 튜플 갯수 조회
select count(*) from safari_sellboard;

-- (4) safari_sellboard 테이블 조회(조회수 높은 순으로 5개)
select no, title, content, time_posted, writer, hits, rank from(
	select no, title, content, time_posted, writer, hits, rank, ceil(rownum/5) as page from(
		select s.sell_no as no, s.sell_title as title, s.sell_content as content, s.sell_time_posted as time_posted, s.id as writer, 
		 s.sell_hits as hits, rank() over (order by sell_hits desc) as rank from safari_sellboard s
	)
)where page=1;

-- (5) safari_buyboard 테이블 조회(조회수 높은 순으로 5개)
select no, title, content, time_posted, writer, hits, rank from(
	select no, title, content, time_posted, writer, hits, rank, ceil(rownum/5) as page from(
		select b.buy_no as no, b.buy_title as title, b.buy_content as content, b.buy_time_posted as time_posted, b.id as writer, 
		b.buy_hits as hits, rank() over (order by buy_hits desc) as rank from safari_buyboard b
	)
)where page=1;

-- (6) safari_freeboard 테이블 조회(조회수 높은 순으로 5개)
select no, title, content, time_posted, writer, hits, rank from(
	select no, title, content, time_posted, writer, hits, rank, ceil(rownum/5) as page from(
		select f.no as no, f.title as title, f.content as content, f.time_posted as time_posted, 
		f.id as writer, f.hits as hits, rank() over (order by hits desc) as rank from safari_freeboard f
	)
)where page=1;










-- ※ 참고 SQL Query문 (실행 안하셔도 됩니다!)
-- 회원 탈퇴시에 회원 테이블을 참조하는 테이블에 데이터가 있을때, 회원탈퇴가 불가능하다. 
-- 회원탈퇴를 가능하게 하기위해서 하단의 alter문을 사용했었다.
-- union을 사용해서 컬럼명이 같은 3개의 테이블을 합쳐보았다.

-- (1) safari_sellboard : 외래키 제약조건 제거
alter table safari_sellboard drop constraint fk_safari_member_id;

-- (2) safari_sellboard : 외래키 제약조건 추가
alter table safari_sellboard 
add constraint fk_safari_member_id foreign key(id) 
references safari_member(id) 
on delete cascade;
     
-- (3) safari_trade_history : 외래키 제약조건 제거
alter table safari_trade_history drop constraint fk_safari_sell_no;

-- (4) safari_trade_history : 외래키 제약조건 추가
alter table safari_trade_history 
add constraint fk_safari_sell_no foreign key(sell_no) 
references safari_sellboard(sell_no) 
on delete cascade;

-- (5) 테이블 3개 통합 (TEST)
select no, title, content, time_posted, writer, hits, rank from(
select no, title, content, time_posted, writer, hits, rank, ceil(rownum/5) as page from(
select no, title, content, time_posted, writer, hits, rank() over (order by hits desc) as rank from(
select no, title, content, time_posted, writer, hits from (
select b.buy_no as no, b.buy_title as title, b.buy_content as content, b.buy_time_posted as time_posted, 
b.id as writer, b.buy_hits as hits
from safari_buyboard b)
	union
		select * from(
		select s.sell_no as no, s.sell_title as title, s.sell_content as content, s.sell_time_posted as time_posted, 
		s.id as writer, s.sell_hits as hits 
		from safari_sellboard s)
			union
				select * from(
				select f.no as no, f.title as title, f.content as content, f.time_posted as time_posted, 
				f.id as writer, f.hits as hits
				from safari_freeboard f)
		)
	)
)where page=1
